package com.lanyou.esb.service;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.FastDateFormat;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;

import com.lanyou.cook.common.Constants;
import com.lanyou.esb.datadictionary.AuditStatusType;
import com.lanyou.esb.web.param.ServiceReuseParam;
import com.lanyou.esb.web.serviceCheck.vo.ServiceReuseDegreeVo;

/**
 * 服务排查
 * 
 * @author Davey.wu
 */
@Service
public class ServiceCheckService {
	@PersistenceContext
	private EntityManager entityManager;

	/** 时间格式：yyyy-MM-dd HH:mm:ss **/
	private static FastDateFormat FASTDATEFORMAT_YYYYMMDDHHMMSS = FastDateFormat
			.getInstance(Constants.DATETIME_PATTERN_1);

	/**
	 * 获取服务重用度
	 * 
	 * @param param
	 *            ：查询参数
	 * @param pageable
	 *            ：分页对象
	 * @param userId
	 *            ：用户标识
	 * @return
	 */
	public Page<ServiceReuseDegreeVo> getServiceReuseDegreeInfo(
			ServiceReuseParam param, Pageable pageable, String userId) {
		// 获取总记录数
		BigInteger total = null;
		// 所有重用度
		if (StringUtils.isBlank(param.getReuseDegree())) {
			total = getServiceReuseDegreeTotal4DegreeIsAll(param, userId,
					AuditStatusType.AUDITED.ValueCode);
		}
		// 重用度为0
		else if ("0".equals(param.getReuseDegree())) {
			total = getServiceReuseDegreeTotal4DegreeIsZero(param, userId,
					AuditStatusType.AUDITED.ValueCode);
		}
		// 其它重用度
		else {
			total = getServiceReuseDegreeTotal(param, userId,
					AuditStatusType.AUDITED.ValueCode);
		}
		// 存在符合条件的记录，则取记录
		if (total == null || total.longValue() <= 0) {
			return new PageImpl<ServiceReuseDegreeVo>(
					new ArrayList<ServiceReuseDegreeVo>(0), pageable, 0);
		}
		// 获取记录
		List<Object[]> objects = null;
		// 所有重用度
		if (StringUtils.isBlank(param.getReuseDegree())) {
			objects = getServiceReuseDegree4DegreeIsAll(param, pageable,
					userId, AuditStatusType.AUDITED.ValueCode);
		}
		// 重用度为0
		else if ("0".equals(param.getReuseDegree())) {
			objects = getServiceReuseDegree4DegreeIsZero(param, pageable,
					userId, AuditStatusType.AUDITED.ValueCode);
		}
		// 其它重用度
		else {
			objects = getServiceReuseDegree(param, pageable, userId,
					AuditStatusType.AUDITED.ValueCode);
		}

		List<ServiceReuseDegreeVo> result = new ArrayList<>(objects.size());
		// 转换结果集
		for (Object[] object : objects) {
			ServiceReuseDegreeVo vo = new ServiceReuseDegreeVo();
			vo.setIfId(object[0] != null ? (String) object[0] : "");
			vo.setIfName(object[1] != null ? (String) object[1] : "");
			if (object[2] != null) {
				// yyyy-MM-dd HH:mm:ss
				vo.setCreateTime(FASTDATEFORMAT_YYYYMMDDHHMMSS
						.format((Date) object[2]));
			}
			vo.setIfType(object[3] != null ? (String) object[3] : "");
			vo.setVersion(object[4] != null ? (String) object[4] : "");
			vo.setRemark(object[5] != null ? (String) object[5] : "");
			vo.setSystemName(object[6] != null ? (String) object[6] : "");
			vo.setResueAmount(((BigInteger) object[7]).intValue());
			vo.setFieldName(object[8] != null ? (String) object[8] : "");
			vo.setReuseSystemName(object[9] != null ? (String) object[9] : "");
			result.add(vo);
		}
		Page<ServiceReuseDegreeVo> page = new PageImpl<ServiceReuseDegreeVo>(
				result, pageable, total.longValue());
		return page;
	}

	/**
	 * 获取服务重用度记录
	 * 
	 * @param param
	 *            ：查询参数
	 * @param pageable
	 *            ：分页条件
	 * @param userId
	 *            ：用户标识
	 * @return
	 */
	private List<Object[]> getServiceReuseDegree(ServiceReuseParam param,
			Pageable pageable, String userId, String auditStatus) {
		String sql = "SELECT V0.IF_HTTP_ID AS interfaceId,   "
				+ "V0.IF_NAME AS interfaceName,  "
				+ "V0.CREATED_DATE AS createdDate,   "
				+ "V0.IF_TYPE AS interfaceType,  "
				+ "V0.IF_VERSION AS VERSION,   "
				+ "V0.REMARK AS remark,   "
				+ "(SELECT s.SYSTEM_NAME FROM t_cook_bu_field_system s WHERE s.FIELD_SYSTEM_ID=V0.FIELD_SYSTEM_ID) AS systemName,   "
				+ "(SELECT COUNT(T.IF_TOKEN_ID) AS reuseAmount FROM t_esb_bu_if_token t WHERE t.IF_ID=V0.IF_HTTP_ID) AS reuseAmount,   "
				+ "(SELECT f.FIELD_NAME FROM t_cook_bu_field_system s,t_cook_db_field f WHERE s.FIELD_SYSTEM_ID=V0.FIELD_SYSTEM_ID AND s.FIELD_ID=f.FIELD_ID) AS fieldName,   "
				+ "(SELECT GROUP_CONCAT(s.SYSTEM_NAME) FROM t_cook_bu_field_system s, t_esb_bu_if_token t0  WHERE s.FIELD_SYSTEM_ID=t0.IF_CALL_SYSTEM_ID AND t0.IF_ID=V0.IF_HTTP_ID) AS reuseSystemName "
				+ "FROM ( "
				+ "	SELECT *  "
				+ "	FROM  "
				+ "		(SELECT h0.IF_HTTP_ID,h0.IF_NAME,h0.IF_VERSION,h0.FIELD_SYSTEM_ID,h0.CREATED_DATE,h0.IF_TYPE,h0.REMARK,  "
				+ "		(SELECT COUNT(s11.FIELD_SYSTEM_ID) FROM t_cook_bu_field_system s11, t_esb_bu_if_token t01   "
				+ "			WHERE s11.FIELD_SYSTEM_ID=t01.IF_CALL_SYSTEM_ID AND t01.IF_ID=h0.IF_HTTP_ID) AS totalNum   "
				+ "			FROM t_esb_bu_if_http h0  WHERE 1=1 ";
		// 添加接口审核状态
		if (StringUtils.isNotBlank(auditStatus)) {
			sql += "AND h0.AUDIT_STATUS =:auditStatus ";
		}
		// 添加接口名称
		if (StringUtils.isNotBlank(param.getIfName())) {
			sql += "AND h0.IF_NAME LIKE :ifName  ";
		}
		// 添加接口类型
		if (StringUtils.isNotBlank(param.getIfType())) {
			sql += "AND h0.IF_TYPE =:ifType ";
		}
		// 添加开始时间
		if (StringUtils.isNotBlank(param.getStartDateTime())) {
			sql += "AND h0.CREATED_DATE >=:startDate ";
		}
		// 添加结束时间
		if (StringUtils.isNotBlank(param.getEndDateTime())) {
			sql += "AND h0.CREATED_DATE <=:endDate ";
		}
		// 添加用户标识
		if (StringUtils.isNotBlank(userId)) {
			sql += "AND EXISTS(SELECT * FROM t_cook_bu_field_system S0,t_cook_bu_user_system U0  "
					+ "				WHERE S0.FIELD_SYSTEM_ID=h0.FIELD_SYSTEM_ID  "
					+ "				AND S0.FIELD_SYSTEM_ID=U0.FIELD_SYSTEM_ID AND U0.USER_ID=:userId ) ";
		}
		sql += ") AS V1 ";
		// 添加重用度
		if (StringUtils.isNotBlank(param.getReuseDegree())) {
			if ("0".equals(param.getReuseDegree())
					|| "1".equals(param.getReuseDegree())) {
				sql += "WHERE totalNum=:degree ";
			} else {
				sql += "WHERE totalNum>=:degree ";
			}

		}

		sql += " LIMIT :pageNumber,:pageSize ) AS V0 ";
		Query query = entityManager.createNativeQuery(sql);

		// 添加接口审核状态参数
		if (StringUtils.isNotBlank(auditStatus)) {
			query.setParameter("auditStatus", auditStatus);
		}
		// 添加接口名称参数
		if (StringUtils.isNotBlank(param.getIfName())) {
			query.setParameter("ifName", param.getIfName());
		}
		// 添加接口类型参数
		if (StringUtils.isNotBlank(param.getIfType())) {
			query.setParameter("ifType", param.getIfType());
		}
		// 添加开始时间参数
		if (StringUtils.isNotBlank(param.getStartDateTime())) {
			query.setParameter("startDate", param.getStartDateTime());
		}
		// 添加结束时间参数
		if (StringUtils.isNotBlank(param.getEndDateTime())) {
			query.setParameter("endDate", param.getEndDateTime());
		}
		// 添加用户标识参数
		if (StringUtils.isNotBlank(userId)) {
			query.setParameter("userId", userId);
		}
		// 添加重用度参数
		if (StringUtils.isNotBlank(param.getReuseDegree())) {
			query.setParameter("degree", param.getReuseDegree());
		}
		query.setParameter("pageNumber",
				pageable.getPageNumber() * pageable.getPageSize());
		query.setParameter("pageSize", pageable.getPageSize());
		// 获取结果集
		List<Object[]> rows = query.getResultList();
		return rows;
	}

	/**
	 * 获取服务重用度记录,重用度为所有
	 * 
	 * @param param
	 *            ：查询参数
	 * @param pageable
	 *            ：分页条件
	 * @param userId
	 *            ：用户标识
	 * @return
	 */
	private List<Object[]> getServiceReuseDegree4DegreeIsAll(
			ServiceReuseParam param, Pageable pageable, String userId,
			String auditStatus) {
		String sql = "SELECT V0.IF_HTTP_ID AS interfaceId,   "
				+ "V0.IF_NAME AS interfaceName,  "
				+ "V0.CREATED_DATE AS createdDate,   "
				+ "V0.IF_TYPE AS interfaceType,  "
				+ "V0.IF_VERSION AS VERSION,   "
				+ "V0.REMARK AS remark,   "
				+ "(SELECT s.SYSTEM_NAME FROM t_cook_bu_field_system s WHERE s.FIELD_SYSTEM_ID=V0.FIELD_SYSTEM_ID) AS systemName,   "
				+ "(SELECT COUNT(T.IF_TOKEN_ID) AS reuseAmount FROM t_esb_bu_if_token t WHERE t.IF_ID=V0.IF_HTTP_ID) AS reuseAmount,   "
				+ "(SELECT f.FIELD_NAME FROM t_cook_bu_field_system s,t_cook_db_field f WHERE s.FIELD_SYSTEM_ID=V0.FIELD_SYSTEM_ID AND s.FIELD_ID=f.FIELD_ID) AS fieldName,   "
				+ "(SELECT GROUP_CONCAT(s.SYSTEM_NAME) FROM t_cook_bu_field_system s, t_esb_bu_if_token t0  WHERE s.FIELD_SYSTEM_ID=t0.IF_CALL_SYSTEM_ID AND t0.IF_ID=V0.IF_HTTP_ID) AS reuseSystemName "
				+ "FROM ( "
				+ "	SELECT *  "
				+ "	FROM  "
				+ "		(SELECT h0.IF_HTTP_ID,h0.IF_NAME,h0.IF_VERSION,h0.FIELD_SYSTEM_ID,h0.CREATED_DATE,h0.IF_TYPE,h0.REMARK  "
				+ "			FROM t_esb_bu_if_http h0  WHERE 1=1 ";
		// 添加接口审核状态
		if (StringUtils.isNotBlank(auditStatus)) {
			sql += "AND h0.AUDIT_STATUS =:auditStatus ";
		}
		// 添加接口名称
		if (StringUtils.isNotBlank(param.getIfName())) {
			sql += "AND h0.IF_NAME LIKE :ifName  ";
		}
		// 添加接口类型
		if (StringUtils.isNotBlank(param.getIfType())) {
			sql += "AND h0.IF_TYPE =:ifType ";
		}
		// 添加开始时间
		if (StringUtils.isNotBlank(param.getStartDateTime())) {
			sql += "AND h0.CREATED_DATE >=:startDate ";
		}
		// 添加结束时间
		if (StringUtils.isNotBlank(param.getEndDateTime())) {
			sql += "AND h0.CREATED_DATE <=:endDate ";
		}
		// 添加用户标识
		if (StringUtils.isNotBlank(userId)) {
			sql += "AND EXISTS(SELECT * FROM t_cook_bu_field_system S0,t_cook_bu_user_system U0  "
					+ "				WHERE S0.FIELD_SYSTEM_ID=h0.FIELD_SYSTEM_ID  "
					+ "				AND S0.FIELD_SYSTEM_ID=U0.FIELD_SYSTEM_ID AND U0.USER_ID=:userId ) ";
		}
		sql += ") AS V1 ";
		sql += " LIMIT :pageNumber,:pageSize ) AS V0 ";
		Query query = entityManager.createNativeQuery(sql);

		// 添加接口审核状态参数
		if (StringUtils.isNotBlank(auditStatus)) {
			query.setParameter("auditStatus", auditStatus);
		}
		// 添加接口名称参数
		if (StringUtils.isNotBlank(param.getIfName())) {
			query.setParameter("ifName", param.getIfName());
		}
		// 添加接口类型参数
		if (StringUtils.isNotBlank(param.getIfType())) {
			query.setParameter("ifType", param.getIfType());
		}
		// 添加开始时间参数
		if (StringUtils.isNotBlank(param.getStartDateTime())) {
			query.setParameter("startDate", param.getStartDateTime());
		}
		// 添加结束时间参数
		if (StringUtils.isNotBlank(param.getEndDateTime())) {
			query.setParameter("endDate", param.getEndDateTime());
		}
		// 添加用户标识参数
		if (StringUtils.isNotBlank(userId)) {
			query.setParameter("userId", userId);
		}
		query.setParameter("pageNumber",
				pageable.getPageNumber() * pageable.getPageSize());
		query.setParameter("pageSize", pageable.getPageSize());
		// 获取结果集
		List<Object[]> rows = query.getResultList();
		return rows;
	}

	/**
	 * 获取服务重用度记录,重用度为0
	 * 
	 * @param param
	 *            ：查询参数
	 * @param pageable
	 *            ：分页条件
	 * @param userId
	 *            ：用户标识
	 * @return
	 */
	private List<Object[]> getServiceReuseDegree4DegreeIsZero(
			ServiceReuseParam param, Pageable pageable, String userId,
			String auditStatus) {
		String sql = "SELECT V0.IF_HTTP_ID AS interfaceId,   "
				+ "V0.IF_NAME AS interfaceName,  "
				+ "V0.CREATED_DATE AS createdDate,   "
				+ "V0.IF_TYPE AS interfaceType,  "
				+ "V0.IF_VERSION AS VERSION,   "
				+ "V0.REMARK AS remark,   "
				+ "(SELECT s.SYSTEM_NAME FROM t_cook_bu_field_system s WHERE s.FIELD_SYSTEM_ID=V0.FIELD_SYSTEM_ID) AS systemName,   "
				+ "0 AS reuseAmount,   "
				+ "(SELECT f.FIELD_NAME FROM t_cook_bu_field_system s,t_cook_db_field f WHERE s.FIELD_SYSTEM_ID=V0.FIELD_SYSTEM_ID AND s.FIELD_ID=f.FIELD_ID) AS fieldName,   "
				+ "'' AS reuseSystemName "
				+ "FROM ( "
				+ "	SELECT *  "
				+ "	FROM  "
				+ "		(SELECT h0.IF_HTTP_ID,h0.IF_NAME,h0.IF_VERSION,h0.FIELD_SYSTEM_ID,h0.CREATED_DATE,h0.IF_TYPE,h0.REMARK  "
				+ "			FROM t_esb_bu_if_http h0 "
				+ "			WHERE NOT EXISTS(SELECT t01.IF_ID  "
				+ "			FROM t_esb_bu_if_token t01   "
				+ "			WHERE t01.IF_ID=h0.IF_HTTP_ID)   ";
		// 添加接口审核状态
		if (StringUtils.isNotBlank(auditStatus)) {
			sql += "AND h0.AUDIT_STATUS =:auditStatus ";
		}
		// 添加接口名称
		if (StringUtils.isNotBlank(param.getIfName())) {
			sql += "AND h0.IF_NAME LIKE :ifName  ";
		}
		// 添加接口类型
		if (StringUtils.isNotBlank(param.getIfType())) {
			sql += "AND h0.IF_TYPE =:ifType ";
		}
		// 添加开始时间
		if (StringUtils.isNotBlank(param.getStartDateTime())) {
			sql += "AND h0.CREATED_DATE >=:startDate ";
		}
		// 添加结束时间
		if (StringUtils.isNotBlank(param.getEndDateTime())) {
			sql += "AND h0.CREATED_DATE <=:endDate ";
		}
		// 添加用户标识
		if (StringUtils.isNotBlank(userId)) {
			sql += "AND EXISTS(SELECT * FROM t_cook_bu_field_system S0,t_cook_bu_user_system U0  "
					+ "				WHERE S0.FIELD_SYSTEM_ID=h0.FIELD_SYSTEM_ID  "
					+ "				AND S0.FIELD_SYSTEM_ID=U0.FIELD_SYSTEM_ID AND U0.USER_ID=:userId ) ";
		}
		sql += ") AS V1 ";
		sql += " LIMIT :pageNumber,:pageSize ) AS V0 ";
		Query query = entityManager.createNativeQuery(sql);

		// 添加接口审核状态参数
		if (StringUtils.isNotBlank(auditStatus)) {
			query.setParameter("auditStatus", auditStatus);
		}
		// 添加接口名称参数
		if (StringUtils.isNotBlank(param.getIfName())) {
			query.setParameter("ifName", param.getIfName());
		}
		// 添加接口类型参数
		if (StringUtils.isNotBlank(param.getIfType())) {
			query.setParameter("ifType", param.getIfType());
		}
		// 添加开始时间参数
		if (StringUtils.isNotBlank(param.getStartDateTime())) {
			query.setParameter("startDate", param.getStartDateTime());
		}
		// 添加结束时间参数
		if (StringUtils.isNotBlank(param.getEndDateTime())) {
			query.setParameter("endDate", param.getEndDateTime());
		}
		// 添加用户标识参数
		if (StringUtils.isNotBlank(userId)) {
			query.setParameter("userId", userId);
		}
		query.setParameter("pageNumber",
				pageable.getPageNumber() * pageable.getPageSize());
		query.setParameter("pageSize", pageable.getPageSize());
		// 获取结果集
		List<Object[]> rows = query.getResultList();
		return rows;
	}

	/**
	 * 获取服务重用度总记录数
	 * 
	 * @param param
	 *            ：查询参数
	 * @param userId
	 *            ：用户标识
	 * @return
	 */
	private BigInteger getServiceReuseDegreeTotal(ServiceReuseParam param,
			String userId, String auditStatus) {
		String sql = "SELECT COUNT(V0.IF_HTTP_ID) AS totalNum "
				+ "FROM  ( "
				+ "	SELECT V1.IF_HTTP_ID FROM  ( "
				+ "			SELECT h0.IF_HTTP_ID,   "
				+ "			(SELECT COUNT(s11.FIELD_SYSTEM_ID)  "
				+ "			FROM t_cook_bu_field_system s11, t_esb_bu_if_token t01   "
				+ "			WHERE s11.FIELD_SYSTEM_ID=t01.IF_CALL_SYSTEM_ID  "
				+ "			AND t01.IF_ID=h0.IF_HTTP_ID) AS totalNum   "
				+ "			FROM t_esb_bu_if_http h0  WHERE 1=1   ";
		// 添加接口审核状态
		if (StringUtils.isNotBlank(auditStatus)) {
			sql += "AND h0.AUDIT_STATUS =:auditStatus ";
		}
		// 添加接口名称
		if (StringUtils.isNotBlank(param.getIfName())) {
			sql += "AND h0.IF_NAME LIKE :ifName  ";
		}
		// 添加接口类型
		if (StringUtils.isNotBlank(param.getIfType())) {
			sql += "AND h0.IF_TYPE =:ifType ";
		}
		// 添加开始时间
		if (StringUtils.isNotBlank(param.getStartDateTime())) {
			sql += "AND h0.CREATED_DATE >=:startDate ";
		}
		// 添加结束时间
		if (StringUtils.isNotBlank(param.getEndDateTime())) {
			sql += "AND h0.CREATED_DATE <=:endDate ";
		}
		// 添加用户标识
		if (StringUtils.isNotBlank(userId)) {
			sql += "AND EXISTS(SELECT * FROM t_cook_bu_field_system S0,t_cook_bu_user_system U0  "
					+ "				WHERE S0.FIELD_SYSTEM_ID=h0.FIELD_SYSTEM_ID  "
					+ "				AND S0.FIELD_SYSTEM_ID=U0.FIELD_SYSTEM_ID AND U0.USER_ID=:userId ) ";
		}
		sql += ") AS V1 ";
		// 添加重用度
		if (StringUtils.isNotBlank(param.getReuseDegree())) {
			if ("0".equals(param.getReuseDegree())
					|| "1".equals(param.getReuseDegree())) {
				sql += "WHERE totalNum=:degree ";
			} else {
				sql += "WHERE totalNum >=:degree ";
			}

		}
		sql += ") AS V0 ";
		Query query = entityManager.createNativeQuery(sql);

		// 添加接口审核状态参数
		if (StringUtils.isNotBlank(auditStatus)) {
			query.setParameter("auditStatus", auditStatus);
		}
		// 添加接口名称参数
		if (StringUtils.isNotBlank(param.getIfName())) {
			query.setParameter("ifName", param.getIfName());
		}
		// 添加接口类型参数
		if (StringUtils.isNotBlank(param.getIfType())) {
			query.setParameter("ifType", param.getIfType());
		}
		// 添加开始时间参数
		if (StringUtils.isNotBlank(param.getStartDateTime())) {
			query.setParameter("startDate", param.getStartDateTime());
		}
		// 添加结束时间参数
		if (StringUtils.isNotBlank(param.getEndDateTime())) {
			query.setParameter("endDate", param.getEndDateTime());
		}
		// 添加用户标识参数
		if (StringUtils.isNotBlank(userId)) {
			query.setParameter("userId", userId);
		}
		// 添加重用度参数
		if (StringUtils.isNotBlank(param.getReuseDegree())) {
			query.setParameter("degree", param.getReuseDegree());
		}
		// 获取记录
		List rows = query.getResultList();
		if (rows == null || rows.isEmpty()) {
			return new BigInteger("0");
		}
		BigInteger total = (BigInteger) rows.get(0);
		return (BigInteger) total;
	}

	/**
	 * 获取服务重用度总记录数,重用度为所有类型
	 * 
	 * @param param
	 *            ：查询参数
	 * @param userId
	 *            ：用户标识
	 * @return
	 */
	private BigInteger getServiceReuseDegreeTotal4DegreeIsAll(
			ServiceReuseParam param, String userId, String auditStatus) {
		String sql = "SELECT COUNT(V0.IF_HTTP_ID) AS totalNum " + "FROM  ( "
				+ "	SELECT V1.IF_HTTP_ID FROM  ( "
				+ "			SELECT h0.IF_HTTP_ID  "
				+ "			FROM t_esb_bu_if_http h0  WHERE 1=1   ";
		// 添加接口审核状态
		if (StringUtils.isNotBlank(auditStatus)) {
			sql += "AND h0.AUDIT_STATUS =:auditStatus ";
		}
		// 添加接口名称
		if (StringUtils.isNotBlank(param.getIfName())) {
			sql += "AND h0.IF_NAME LIKE :ifName  ";
		}
		// 添加接口类型
		if (StringUtils.isNotBlank(param.getIfType())) {
			sql += "AND h0.IF_TYPE =:ifType ";
		}
		// 添加开始时间
		if (StringUtils.isNotBlank(param.getStartDateTime())) {
			sql += "AND h0.CREATED_DATE >=:startDate ";
		}
		// 添加结束时间
		if (StringUtils.isNotBlank(param.getEndDateTime())) {
			sql += "AND h0.CREATED_DATE <=:endDate ";
		}
		// 添加用户标识
		if (StringUtils.isNotBlank(userId)) {
			sql += "AND EXISTS(SELECT * FROM t_cook_bu_field_system S0,t_cook_bu_user_system U0  "
					+ "				WHERE S0.FIELD_SYSTEM_ID=h0.FIELD_SYSTEM_ID  "
					+ "				AND S0.FIELD_SYSTEM_ID=U0.FIELD_SYSTEM_ID AND U0.USER_ID=:userId ) ";
		}
		sql += ") AS V1 ";
		sql += ") AS V0 ";
		Query query = entityManager.createNativeQuery(sql);

		// 添加接口审核状态参数
		if (StringUtils.isNotBlank(auditStatus)) {
			query.setParameter("auditStatus", auditStatus);
		}
		// 添加接口名称参数
		if (StringUtils.isNotBlank(param.getIfName())) {
			query.setParameter("ifName", param.getIfName());
		}
		// 添加接口类型参数
		if (StringUtils.isNotBlank(param.getIfType())) {
			query.setParameter("ifType", param.getIfType());
		}
		// 添加开始时间参数
		if (StringUtils.isNotBlank(param.getStartDateTime())) {
			query.setParameter("startDate", param.getStartDateTime());
		}
		// 添加结束时间参数
		if (StringUtils.isNotBlank(param.getEndDateTime())) {
			query.setParameter("endDate", param.getEndDateTime());
		}
		// 添加用户标识参数
		if (StringUtils.isNotBlank(userId)) {
			query.setParameter("userId", userId);
		}
		// 获取记录
		List rows = query.getResultList();
		if (rows == null || rows.isEmpty()) {
			return new BigInteger("0");
		}
		BigInteger total = (BigInteger) rows.get(0);
		return (BigInteger) total;
	}

	/**
	 * 获取服务重用度总记录数,重用度为0
	 * 
	 * @param param
	 *            ：查询参数
	 * @param userId
	 *            ：用户标识
	 * @return
	 */
	private BigInteger getServiceReuseDegreeTotal4DegreeIsZero(
			ServiceReuseParam param, String userId, String auditStatus) {
		String sql = "SELECT COUNT(V0.IF_HTTP_ID) AS totalNum "
				+ "FROM  ( "
				+ "	SELECT V1.IF_HTTP_ID FROM  ( "
				+ "			SELECT h0.IF_HTTP_ID "
				+ "			FROM t_esb_bu_if_http h0 "
				+ "			WHERE NOT EXISTS(SELECT t01.IF_ID  "
				+ "			FROM  t_esb_bu_if_token t01   "
				+ "			WHERE t01.IF_ID=h0.IF_HTTP_ID)   ";
		// 添加接口审核状态
		if (StringUtils.isNotBlank(auditStatus)) {
			sql += "AND h0.AUDIT_STATUS =:auditStatus ";
		}
		// 添加接口名称
		if (StringUtils.isNotBlank(param.getIfName())) {
			sql += "AND h0.IF_NAME LIKE :ifName  ";
		}
		// 添加接口类型
		if (StringUtils.isNotBlank(param.getIfType())) {
			sql += "AND h0.IF_TYPE =:ifType ";
		}
		// 添加开始时间
		if (StringUtils.isNotBlank(param.getStartDateTime())) {
			sql += "AND h0.CREATED_DATE >=:startDate ";
		}
		// 添加结束时间
		if (StringUtils.isNotBlank(param.getEndDateTime())) {
			sql += "AND h0.CREATED_DATE <=:endDate ";
		}
		// 添加用户标识
		if (StringUtils.isNotBlank(userId)) {
			sql += "AND EXISTS(SELECT * FROM t_cook_bu_field_system S0,t_cook_bu_user_system U0  "
					+ "				WHERE S0.FIELD_SYSTEM_ID=h0.FIELD_SYSTEM_ID  "
					+ "				AND S0.FIELD_SYSTEM_ID=U0.FIELD_SYSTEM_ID AND U0.USER_ID=:userId ) ";
		}
		sql += ") AS V1 ";
		sql += ") AS V0 ";
		Query query = entityManager.createNativeQuery(sql);

		// 添加接口审核状态参数
		if (StringUtils.isNotBlank(auditStatus)) {
			query.setParameter("auditStatus", auditStatus);
		}
		// 添加接口名称参数
		if (StringUtils.isNotBlank(param.getIfName())) {
			query.setParameter("ifName", param.getIfName());
		}
		// 添加接口类型参数
		if (StringUtils.isNotBlank(param.getIfType())) {
			query.setParameter("ifType", param.getIfType());
		}
		// 添加开始时间参数
		if (StringUtils.isNotBlank(param.getStartDateTime())) {
			query.setParameter("startDate", param.getStartDateTime());
		}
		// 添加结束时间参数
		if (StringUtils.isNotBlank(param.getEndDateTime())) {
			query.setParameter("endDate", param.getEndDateTime());
		}
		// 添加用户标识参数
		if (StringUtils.isNotBlank(userId)) {
			query.setParameter("userId", userId);
		}
		// 获取记录
		List rows = query.getResultList();
		if (rows == null || rows.isEmpty()) {
			return new BigInteger("0");
		}
		BigInteger total = (BigInteger) rows.get(0);
		return (BigInteger) total;
	}
}
